/*
 * 文件名：CommonDaoImp.java
 * 版权：Copyright by www.wootide.com
 * 描述：
 * 修改人：ozm
 * 修改时间：2013-1-28
 * 跟踪单号：
 * 修改单号：
 * 修改内容：
 */
package com.woolink.per.common.dao.impl;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;

import com.woolink.per.common.Page;
import com.woolink.per.common.QueryCondition;
import com.woolink.per.common.QueryResults;
import com.woolink.per.common.dao.CommonDao;
import com.woolink.per.common.util.CommonUtil;
import com.woolink.per.log.syslog.RunLog;

public class CommonDaoImpl extends HibernateTemplate implements CommonDao,InitializingBean
{
    //日志对象
    private RunLog runLog;
    
    private List paramNames = new ArrayList();
    
    private List paramValues = new ArrayList();
    
    /**
     * 
     * Description: 分页查询,仅适用于==和like条件
     * Implement: <br>
     * @param page  分页信息
     * @param queryConditon 查询条件
     * @see
     * @return QueryResults 结果集
     */
    @SuppressWarnings("unchecked")
    public QueryResults queryForPages(final Page page,
            final QueryCondition queryConditon)
    {
        // 打印方法入口参数
        runLog.debug("page=" + page);
        runLog.debug("queryConditon" + queryConditon);
        
        @SuppressWarnings("rawtypes")
        List queryResults = (List)execute(new HibernateCallback()
        {
            public Object doInHibernate(Session session) throws SQLException
            {
                Criteria criteria = session.createCriteria(queryConditon.getObjClass());
                
                // 如果查询条件不为空
                if (CommonUtil.conditionExist(queryConditon))
                {
                    String strCondition = "%" + queryConditon.getValue().trim()
                            + "%";
                    String conditionKey = queryConditon.getKey();
                    criteria.add(Restrictions.ilike(conditionKey, strCondition));
                }
                //                        if (null != queryConditon)
                //                        {
                //                            List  conditionKeyList = queryConditon.getConditionKeyList();
                //                            
                //                            if (!CommonUtil.collectionIsNull(conditionKeyList))
                //                            {
                //                                //查询条件不为空
                ////                                Map conditionMap = queryConditon.getConditionMap();
                //                                for (String conditionKey : queryConditon.getConditionKeyList())
                //                                {
                //                                    conditionKeyList.indexOf(conditionKey);
                //                                    //添加查询条件
                ////                                    criteria.add(Restrictions.ilike(conditionKey, conditionMap.get(conditionKey)));
                //                                }
                //                            }
                //                            
                //                            if (!CommonUtil.collectionIsNull(queryConditon.getOrderDescList()))
                //                            {
                //                                //添加降序字段
                //                                for (String descStr : queryConditon.getOrderDescList())
                //                                {
                //                                    criteria.addOrder(Order.desc(descStr));
                //                                }
                //                            }
                //                            
                //                            if (!CommonUtil.collectionIsNull(queryConditon.getOrderAscList()))
                //                            {
                //                                //添加升序字段
                //                                for (String ascStr : queryConditon.getOrderAscList())
                //                                {
                //                                    criteria.addOrder(Order.asc(ascStr));
                //                                }
                //                            }
                //                        }
                ////                     
                // 获得总记录数
                criteria.setProjection(null);
                int totalNum = ((Number)criteria.setProjection(Projections.rowCount())
                        .list()
                        .get(0)).intValue();
                page.setTotalNum(totalNum);
                
                criteria.setProjection(null);
                // 获得分页查询数据
                List results = criteria.setMaxResults(page.getNumPerPage())
                        .setFirstResult(page.getFirstResult())
                        .list();
                return results;
            }
        });
        QueryResults result = new QueryResults();
        result.setResults(queryResults);
        result.setPage(page);
        return result;
    }
    
    /**
     * 添加一个对象
     * Description: <br>
     * Implement: <br>
     * @param obj 新增的用户信息
     * @see
     * @return void
     */
    public void addOneObject(Object obj)
    {
        runLog.debug("obj=" + obj);
        
        this.save(obj);
    }
    
    /**
     * 修改一个对象
     * Description: <br>
     * Implement: <br>
     * @param obj 需要修改对象
     * @see
     * @return void
     */
    public void modOneObject(Object obj)
    {
        runLog.debug("obj=" + obj);
        this.merge(obj);
        this.update(obj);
    }
    
    /**
     * 删除一个对象
     * Description: <br>
     * Implement: <br>
     * @param obj 需要删除对象
     * @see
     * @return void
     */
    public void delOneObject(Object obj)
    {
        runLog.debug("obj=" + obj);
        
        this.delete(obj);
    }
    
    public RunLog getRunLog()
    {
        return runLog;
    }
    
    public void setRunLog(RunLog runLog)
    {
        this.runLog = runLog;
    }
    
    private String getSelect(List selectColumns) throws Exception
    {
        try
        {
            StringBuffer strSelect = new StringBuffer();
            strSelect.append("select ");
            Map column;
            for (int i = 0; i < selectColumns.size(); i++)
            {
                column = (Map)selectColumns.get(i);
                strSelect.append((String)column.get("value"))
                        .append(" as ")
                        .append((String)column.get("alias"))
                        .append(",");
            }
            return strSelect.substring(0, strSelect.length() - 1);
        }
        catch (Exception e)
        {
            throw new Exception(e);
        }
    }
    
    private String getCalculateSelect(List selectColumns) throws Exception
    {
        try
        {
            StringBuffer strSelect = new StringBuffer();
            strSelect.append("select ");
            Map column;
            boolean isCalculate = false;
            for (int i = 0; i < selectColumns.size(); i++)
            {
                column = (Map)selectColumns.get(i);
                if (((String)column.get("calculated")).toLowerCase()
                        .equals("true"))
                {
                    isCalculate = true;
                    strSelect.append((String)column.get("function"))
                            .append(" as ")
                            .append((String)column.get("alias"))
                            .append(",");
                }
                else
                {
                    strSelect.append("'--'");
                    strSelect.append(" as ")
                            .append((String)column.get("alias"))
                            .append(",");
                }
            }
            if (isCalculate)
                return strSelect.substring(0, strSelect.length() - 1);
            else
                return null;
        }
        catch (Exception e)
        {
            throw new Exception(e);
        }
    }
    
    private String getJoinTable(List joinTables) throws Exception
    {
        try
        {
            StringBuffer strJoin = new StringBuffer();
            Map joinTable;
            for (int i = 0; i < joinTables.size(); i++)
            {
                joinTable = (Map)joinTables.get(i);
                strJoin.append((String)joinTable.get("tablename"))
                        .append(" ")
                        .append((String)joinTable.get("alias"))
                        .append(",");
            }
            return strJoin.substring(0, strJoin.length() - 1);
        }
        catch (Exception e)
        {
            throw new Exception(e);
        }
    }
    
    private String getJoinWhere(List joinTables) throws Exception
    {
        try
        {
            StringBuffer strJoinWhere = new StringBuffer();
            strJoinWhere.append("where ");
            Map joinTable;
            for (int i = 0; i < joinTables.size(); i++)
            {
                joinTable = (Map)joinTables.get(i);
                strJoinWhere.append((String)joinTable.get("condition"))
                        .append(" and ");
            }
            return strJoinWhere.substring(0, strJoinWhere.length() - 4);
        }
        catch (Exception e)
        {
            throw new Exception(e);
        }
    }
    
    private String getGroup(List selectColumns) throws Exception
    {
        try
        {
            StringBuffer strSelect = new StringBuffer();
            strSelect.append("group by ");
            Map column;
            for (int i = 0; i < selectColumns.size(); i++)
            {
                column = (Map)selectColumns.get(i);
                String isGroup = (String)column.get("group");
                if (isGroup != null && isGroup.toLowerCase().equals("true"))
                {
                    strSelect.append((String)column.get("value")).append(",");
                }
            }
            if (strSelect.length() == 9)
                return "";
            else
                return strSelect.substring(0, strSelect.length() - 1);
        }
        catch (Exception e)
        {
            throw new Exception(e);
        }
    }
    
    private String getOrder(List selectColumns) throws Exception
    {
        try
        {
            StringBuffer strSelect = new StringBuffer();
            strSelect.append("order by ");
            Map column;
            for (int i = 0; i < selectColumns.size(); i++)
            {
                column = (Map)selectColumns.get(i);
                if (((String)column.get("order")).toLowerCase().equals("true"))
                {
                    strSelect.append((String)column.get("alias")).append(",");
                }
            }
            if (strSelect.length() == 9)
                return "";
            else
                return strSelect.substring(0, strSelect.length() - 1);
        }
        catch (Exception e)
        {
            throw new Exception(e);
        }
    }
    
    private Object valueFieldTreat(String fieldValue, String dataType)
            throws Exception
    {
        Object ret = null;
        
        if (fieldValue == null || fieldValue.trim().equals(""))
        {
            ret = null;
        }
        else if (dataType.toLowerCase().equals("int"))
        {
            ret = new Integer(fieldValue);
        }
        else if (dataType.toLowerCase().equals("datetime"))
        {
            ret = getTimestamp(fieldValue);
        }
        else
        {
            ret = fieldValue;
        }
        //      } else if (dataType.toLowerCase().equals("varchar")) {
        //          ret = "'" + fieldValue + "'";
        //      } else if (dataType.toLowerCase().equals("int")) {
        //          ret = fieldValue;
        //      } else if (dataType.toLowerCase().equals("datetime")) {
        //          ret = dateFieldTreat(fieldValue);
        //      }
        return ret;
    }
    
    private Timestamp getTimestamp(String fieldValue) throws Exception
    {
        return Timestamp.valueOf(fieldValue);
    }
    
    private String getTableName(Map tablePrefix) throws Exception
    {
        try
        {
            String prefix = (String)tablePrefix.get("name");
            String alias = (String)tablePrefix.get("alias");
            
            StringBuffer strTableName = new StringBuffer();
            strTableName.append(prefix).append(" ").append(alias);
            return strTableName.toString();
        }
        catch (Exception e)
        {
            throw new Exception(e);
        }
    }
    
    @SuppressWarnings("unchecked")
    public int getCountBySQLQuery(final String sql, final List selectColumns)
            throws Exception
    {
        Integer count = (Integer)execute(new HibernateCallback()
        {
            public Object doInHibernate(Session session)
                    throws HibernateException
            {
                SQLQuery queryObject = session.createSQLQuery(sql);
                //      applyScalarToSQLQuery(queryObject, selectColumns);
                queryObject.addScalar("totalcount", Hibernate.INTEGER);
                List countList = queryObject.list();
                //log.info("count result="+countList);
                int tmp = 0;
                for (int i = 0; i < countList.size(); i++)
                {
                    tmp = tmp + ((Integer)countList.get(i)).intValue();
                }
                //log.info("all count="+String.valueOf(tmp));
                return new Integer(tmp);
            }
        });
        return count.intValue();
    }
    
    @SuppressWarnings("unchecked")
    public int getCountBySQLQuery(final String sql, final List selectColumns,
            final String[] paramNames, final Object[] values) throws Exception
    {
        if (paramNames.length != values.length)
        {
            throw new IllegalArgumentException(
                    "Length of paramNames array must match length of values array");
        }
        Integer count = (Integer)execute(new HibernateCallback()
        {
            public Object doInHibernate(Session session) throws SQLException
            {
                SQLQuery queryObject = session.createSQLQuery(sql);
                prepareQuery(queryObject);
                if (values != null)
                {
                    for (int i = 0; i < values.length; i++)
                    {
                        applyNamedParameterToQuery(queryObject,
                                paramNames[i],
                                values[i]);
                    }
                }
                //      applyScalarToSQLQuery(queryObject, selectColumns);
                queryObject.addScalar("totalcount", Hibernate.INTEGER);
                List countList = queryObject.list();
              
                //log.info("count result="+countList);
                int tmp = 0;
                for (int i = 0; i < countList.size(); i++)
                {
                    tmp = tmp + ((Integer)countList.get(i)).intValue();
                }
                //log.info("all count="+String.valueOf(tmp));
                return new Integer(tmp);
            }
        });
        return count.intValue();
    }
    
    public List getPageBySQLQuery(final String sql, final List selectColumns,
            final int pageSize, final int startIndex,
            final String[] paramNames, final Object[] values) throws Exception
    {
        if (paramNames.length != values.length)
        {
            throw new IllegalArgumentException(
                    "Length of paramNames array must match length of values array");
        }
        List tmp = (List)execute(new HibernateCallback()
        {
            public Object doInHibernate(Session session)
                    throws HibernateException
            {
                //      session.setFlushMode(FlushMode.NEVER);
                SQLQuery queryObject = session.createSQLQuery(sql);
                prepareQuery(queryObject);
                if (values != null)
                {
                    for (int i = 0; i < values.length; i++)
                    {
                        applyNamedParameterToQuery(queryObject,
                                paramNames[i],
                                values[i]);
                    }
                }
                queryObject.setFirstResult(startIndex).setMaxResults(pageSize);
                applyScalarToSQLQuery(queryObject, selectColumns);
                return queryObject.list();
            }
        });
        //log.info("get from database size="+tmp.size());
        return tmp;
    }
    
    public List getPageBySQLQuery(final String sql, final List selectColumns,
            final int pageSize, final int startIndex) throws Exception
    {
        return (List)execute(new HibernateCallback()
        {
            public Object doInHibernate(Session session)
                    throws HibernateException
            {
                SQLQuery queryObject = session.createSQLQuery(sql);
                queryObject.setFirstResult(startIndex).setMaxResults(pageSize);
                applyScalarToSQLQuery(queryObject, selectColumns);
                return queryObject.list();
            }
        });
    }
    
    public List getCalculateBySQLQuery(final String sql,
            final List selectColumns, final String[] paramNames,
            final Object[] values) throws Exception
    {
        if (paramNames.length != values.length)
        {
            throw new IllegalArgumentException(
                    "Length of paramNames array must match length of values array");
        }
        return (List)execute(new HibernateCallback()
        {
            public Object doInHibernate(Session session)
                    throws HibernateException
            {
                SQLQuery queryObject = session.createSQLQuery(sql);
                prepareQuery(queryObject);
                if (values != null)
                {
                    for (int i = 0; i < values.length; i++)
                    {
                        applyNamedParameterToQuery(queryObject,
                                paramNames[i],
                                values[i]);
                    }
                }
                applyScalarToSQLQueryForCalculate(queryObject, selectColumns);
                List tmpList = queryObject.list();
                List returnList = new ArrayList();
                for (int i = 0; i < selectColumns.size(); i++)
                {
                    returnList.add(i, new Double(0));
                }
                double d = 0;
                Object[] tmpObjs = null;
                for (int i = 0; i < tmpList.size(); i++)
                {
                    tmpObjs = (Object[])tmpList.get(i);
                    for (int j = 0; j < tmpObjs.length; j++)
                    {
                        String isCalculate = (String)((Map)selectColumns.get(j)).get("calculated");
                        String dataType = (String)((Map)selectColumns.get(j)).get("datatype");
                        if (tmpObjs[j] == null)
                            continue;
                        if (isCalculate.toLowerCase().equals("true"))
                        {
                            if (dataType.toLowerCase().equals("double"))
                            {
                                d = ((Double)tmpObjs[j]).doubleValue()
                                        + ((Double)returnList.get(j)).doubleValue();
                            }
                            else if (dataType.toLowerCase().equals("float"))
                            {
                                d = ((Float)tmpObjs[j]).doubleValue()
                                        + ((Double)returnList.get(j)).doubleValue();
                            }
                            else if (dataType.toLowerCase().equals("long"))
                            {
                                d = ((Long)tmpObjs[j]).doubleValue()
                                        + ((Double)returnList.get(j)).doubleValue();
                            }
                            else if (dataType.toLowerCase().equals("int"))
                            {
                                d = ((Integer)tmpObjs[j]).doubleValue()
                                        + ((Double)returnList.get(j)).doubleValue();
                            }
                            returnList.set(j, new Double(d));
                        }
                        else
                        {
                            returnList.set(j, "--");
                        }
                    }
                }
                return returnList;
            }
        });
    }
    
    public List getCalculateBySQLQuery(final String sql,
            final List selectColumns) throws Exception
    {
        return (List)execute(new HibernateCallback()
        {
            public Object doInHibernate(Session session)
                    throws HibernateException
            {
                SQLQuery queryObject = session.createSQLQuery(sql);
                prepareQuery(queryObject);
                applyScalarToSQLQueryForCalculate(queryObject, selectColumns);
                List tmpList = queryObject.list();
                List returnList = new ArrayList();
                for (int i = 0; i < selectColumns.size(); i++)
                {
                    returnList.add(i, new Integer(0));
                }
                double d = 0;
                Object[] tmpObjs = null;
                for (int i = 0; i < tmpList.size(); i++)
                {
                    tmpObjs = (Object[])tmpList.get(i);
                    for (int j = 0; j < tmpObjs.length; j++)
                    {
                        String isCalculate = (String)(((Map)selectColumns.get(j)).get("calculated"));
                        String dataType = (String)((Map)selectColumns.get(j)).get("datatype");
                        if (isCalculate.toLowerCase().equals("true"))
                        {
                            if (dataType.toLowerCase().equals("double"))
                            {
                                d = ((Double)tmpObjs[j]).doubleValue()
                                        + ((Double)returnList.get(j)).doubleValue();
                            }
                            else if (dataType.toLowerCase().equals("float"))
                            {
                                d = ((Float)tmpObjs[j]).doubleValue()
                                        + ((Double)returnList.get(j)).doubleValue();
                            }
                            else if (dataType.toLowerCase().equals("long"))
                            {
                                d = ((Long)tmpObjs[j]).doubleValue()
                                        + ((Double)returnList.get(j)).doubleValue();
                            }
                            else if (dataType.toLowerCase().equals("int"))
                            {
                                d = ((Integer)tmpObjs[j]).doubleValue()
                                        + ((Double)returnList.get(j)).doubleValue();
                            }
                            returnList.set(j, new Double(d));
                        }
                        else
                        {
                            returnList.set(j, "--");
                        }
                    }
                }
                return returnList;
            }
        });
    }
    
    private void applyScalarToSQLQuery(SQLQuery queryObject, List selectColumns)
            throws HibernateException
    {
        Map tmp;
        for (int i = 0; i < selectColumns.size(); i++)
        {
            tmp = (Map)selectColumns.get(i);
            String columnName = (String)tmp.get("alias");
            String dataType = (String)tmp.get("datatype");
            if (dataType.toLowerCase().equals("varchar"))
            {
                queryObject.addScalar(columnName, Hibernate.STRING);
            }
            else if (dataType.toLowerCase().equals("int"))
            {
                queryObject.addScalar(columnName, Hibernate.INTEGER);
            }
            else if (dataType.toLowerCase().equals("long"))
            {
                queryObject.addScalar(columnName, Hibernate.LONG);
            }
            else if (dataType.toLowerCase().equals("float"))
            {
                queryObject.addScalar(columnName, Hibernate.FLOAT);
            }
            else if (dataType.toLowerCase().equals("double"))
            {
                queryObject.addScalar(columnName, Hibernate.DOUBLE);
            }
            else if (dataType.toLowerCase().equals("datetime"))
            {
                queryObject.addScalar(columnName, Hibernate.TIMESTAMP);
            }
            else
            {
                throw new HibernateException("未定义类型" + dataType);
            }
        }
    }
    
    private void applyScalarToSQLQueryForCalculate(SQLQuery queryObject,
            List selectColumns) throws HibernateException
    {
        Map tmp;
        for (int i = 0; i < selectColumns.size(); i++)
        {
            tmp = (Map)selectColumns.get(i);
            String columnName = (String)tmp.get("alias");
            String dataType = (String)tmp.get("datatype");
            boolean calculated = new Boolean((String)tmp.get("calculated")).booleanValue();
            if (!calculated)
            {
                queryObject.addScalar(columnName, Hibernate.STRING);
            }
            else
            {
                if (dataType.toLowerCase().equals("int"))
                {
                    queryObject.addScalar(columnName, Hibernate.INTEGER);
                }
                else if (dataType.toLowerCase().equals("long"))
                {
                    queryObject.addScalar(columnName, Hibernate.LONG);
                }
                else if (dataType.toLowerCase().equals("float"))
                {
                    queryObject.addScalar(columnName, Hibernate.FLOAT);
                }
                else if (dataType.toLowerCase().equals("double"))
                {
                    queryObject.addScalar(columnName, Hibernate.DOUBLE);
                }
                else
                {
                    throw new HibernateException("未定义类型" + dataType);
                }
            }
        }
    }
    
    public List getParamNames()
    {
        return paramNames;
    }
    
    public void setParamNames(List paramNames)
    {
        this.paramNames = paramNames;
    }
    
    public List getParamValues()
    {
        return paramValues;
    }
    
    public void setParamValues(List paramValues)
    {
        this.paramValues = paramValues;
    }
}
